import os
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill
from django.http import HttpResponse, JsonResponse
from django.shortcuts import render, redirect
from django.urls import path
from django.contrib import messages
from django.contrib.admin.views.decorators import staff_member_required
from django.utils.translation import gettext as _
from django.utils import timezone
from django.db import transaction
from django.conf import settings

from .models import Category, Product, SKU
from .forms import ProductExcelUploadForm

@staff_member_required
def generate_excel_template(request):
    """生成Excel模板文件"""
    # 创建一个新的Excel工作簿
    wb = openpyxl.Workbook()
    
    # 获取活动工作表
    ws = wb.active
    ws.title = "Products"
    
    # 设置表头样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    
    # 添加表头
    headers = [
        "分类ID", "分类名称", "商品名称", "商品描述", 
        "商品图片URL", "SKU名称", "SKU价格", "SKU库存"
    ]
    
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
    
    # 设置列宽
    column_widths = [10, 15, 30, 40, 40, 20, 10, 10]
    for i, width in enumerate(column_widths, 1):
        ws.column_dimensions[openpyxl.utils.get_column_letter(i)].width = width
    
    # 添加示例数据
    categories = Category.objects.filter(is_active=True)
    if categories.exists():
        for i, category in enumerate(categories[:3], 2):
            ws.cell(row=i, column=1, value=category.id)
            ws.cell(row=i, column=2, value=category.name)
            ws.cell(row=i, column=3, value="示例商品名称")
            ws.cell(row=i, column=4, value="这是一个示例商品描述，请替换为实际内容。")
            ws.cell(row=i, column=5, value="http://example.com/image.jpg")
            ws.cell(row=i, column=6, value="标准规格")
            ws.cell(row=i, column=7, value=99.00)
            ws.cell(row=i, column=8, value=100)
    else:
        # 如果没有分类，添加一个示例行
        ws.cell(row=2, column=1, value="")
        ws.cell(row=2, column=2, value="请先创建分类")
        ws.cell(row=2, column=3, value="示例商品名称")
        ws.cell(row=2, column=4, value="这是一个示例商品描述，请替换为实际内容。")
        ws.cell(row=2, column=5, value="http://example.com/image.jpg")
        ws.cell(row=2, column=6, value="标准规格")
        ws.cell(row=2, column=7, value=99.00)
        ws.cell(row=2, column=8, value=100)
    
    # 添加说明工作表
    ws_instructions = wb.create_sheet(title="Instructions")
    instructions = [
        ["商品批量导入说明"],
        [""],
        ["1. 请按照模板格式填写商品信息"],
        ["2. 分类ID和分类名称必须对应系统中已有的分类"],
        ["3. 商品名称为必填项"],
        ["4. 商品描述可以为空"],
        ["5. 商品图片URL可以为空，也可以填写网络图片地址"],
        ["6. SKU名称为必填项，同一商品可以有多个SKU（在不同行填写）"],
        ["7. SKU价格必须为数字"],
        ["8. SKU库存必须为整数"],
        [""],
        ["当前系统中的分类:"],
    ]
    
    for row_num, row_data in enumerate(instructions, 1):
        for col_num, cell_value in enumerate(row_data, 1):
            cell = ws_instructions.cell(row=row_num, column=col_num, value=cell_value)
            if row_num == 1:
                cell.font = Font(bold=True, size=14)
    
    # 添加当前系统中的分类信息
    row_num = len(instructions) + 1
    for category in categories:
        ws_instructions.cell(row=row_num, column=1, value=f"ID: {category.id}")
        ws_instructions.cell(row=row_num, column=2, value=f"名称: {category.name}")
        row_num += 1
    
    # 设置列宽
    ws_instructions.column_dimensions['A'].width = 30
    ws_instructions.column_dimensions['B'].width = 50
    
    # 创建响应
    response = HttpResponse(
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = 'attachment; filename=product_import_template.xlsx'
    
    # 保存工作簿到响应
    wb.save(response)
    
    return response

@staff_member_required
def upload_products_from_excel(request):
    """处理Excel文件上传并导入商品"""
    if request.method == 'POST':
        form = ProductExcelUploadForm(request.POST, request.FILES)
        if form.is_valid():
            excel_file = request.FILES['excel_file']
            
            # 检查文件扩展名
            if not excel_file.name.endswith('.xlsx'):
                messages.error(request, _("Please upload a valid Excel file (.xlsx)"))
                return render(request, 'admin/product/product/upload_excel.html', {'form': form})
            
            try:
                # 打开Excel文件
                wb = openpyxl.load_workbook(excel_file)
                ws = wb.active
                
                # 获取表头
                headers = [cell.value for cell in ws[1]]
                
                # 验证表头
                required_headers = ["分类ID", "分类名称", "商品名称", "SKU名称", "SKU价格", "SKU库存"]
                for header in required_headers:
                    if header not in headers:
                        messages.error(request, _(f"Missing required column: {header}"))
                        return render(request, 'admin/product/product/upload_excel.html', {'form': form})
                
                # 获取列索引
                category_id_idx = headers.index("分类ID")
                category_name_idx = headers.index("分类名称")
                product_name_idx = headers.index("商品名称")
                product_desc_idx = headers.index("商品描述") if "商品描述" in headers else None
                product_image_idx = headers.index("商品图片URL") if "商品图片URL" in headers else None
                sku_name_idx = headers.index("SKU名称")
                sku_price_idx = headers.index("SKU价格")
                sku_stock_idx = headers.index("SKU库存")
                
                # 处理数据行
                success_count = 0
                error_count = 0
                products_created = []
                
                with transaction.atomic():
                    current_product = None
                    current_product_name = None
                    
                    # 从第二行开始处理数据
                    for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), 2):
                        try:
                            # 检查行是否为空
                            if all(cell is None or cell == "" for cell in row):
                                continue
                            
                            # 获取数据
                            category_id = row[category_id_idx]
                            category_name = row[category_name_idx]
                            product_name = row[product_name_idx]
                            product_desc = row[product_desc_idx] if product_desc_idx is not None else ""
                            product_image = row[product_image_idx] if product_image_idx is not None else ""
                            sku_name = row[sku_name_idx]
                            sku_price = row[sku_price_idx]
                            sku_stock = row[sku_stock_idx]
                            
                            # 验证必填字段
                            if not product_name or not sku_name or sku_price is None or sku_stock is None:
                                error_count += 1
                                continue
                            
                            # 验证分类
                            try:
                                if category_id:
                                    category = Category.objects.get(id=category_id)
                                elif category_name:
                                    category = Category.objects.get(name=category_name)
                                else:
                                    raise Category.DoesNotExist
                            except Category.DoesNotExist:
                                error_count += 1
                                continue
                            
                            # 检查是否是同一个商品的不同SKU
                            if current_product_name != product_name:
                                # 创建新商品
                                current_product = Product.objects.create(
                                    category=category,
                                    name=product_name,
                                    description=product_desc or "",
                                    is_active=True
                                )
                                
                                # 如果有图片URL，可以在这里处理
                                if product_image:
                                    # 这里可以添加从URL下载图片的逻辑
                                    pass
                                
                                current_product_name = product_name
                                products_created.append(current_product)
                            
                            # 创建SKU
                            SKU.objects.create(
                                product=current_product,
                                name=sku_name,
                                price=float(sku_price),
                                stock=int(sku_stock),
                                is_active=True
                            )
                            
                            success_count += 1
                            
                        except Exception as e:
                            error_count += 1
                            continue
                
                # 提示导入结果
                if success_count > 0:
                    messages.success(request, _(f"Successfully imported {success_count} products/SKUs."))
                if error_count > 0:
                    messages.warning(request, _(f"Failed to import {error_count} rows due to errors."))
                
                return redirect('admin:product_product_changelist')
            
            except Exception as e:
                messages.error(request, _(f"Error processing Excel file: {str(e)}"))
    else:
        form = ProductExcelUploadForm()
    
    return render(request, 'admin/product/product/upload_excel.html', {'form': form})

# 定义URL模式
urlpatterns = [
    path('excel-template/', generate_excel_template, name='product_excel_template'),
    path('upload-excel/', upload_products_from_excel, name='product_upload_excel'),
]
